package com.ysstech.common.util;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.ysstech.common.entity.ColumnInfo;
import com.ysstech.common.entity.IndexInfo;
import com.ysstech.common.entity.TableInfo;
import com.ysstech.common.enums.YssEnum;
import com.ysstech.common.exception.BusinessException;
import io.swagger.models.auth.In;
import lombok.extern.slf4j.Slf4j;
import oracle.jdbc.driver.OracleConnection;
import org.apache.commons.lang.StringUtils;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

@Slf4j
public class JdbcUtil {

    //获得驱动
  /*  private static String DRIVER = "oracle.jdbc.driver.OracleDriver";
    //获得url
    private static String URL = "jdbc:oracle:thin:@192.168.32.225:1521:orcl";
    //获得连接数据库的用户名
    private static String USER = "indbadmin";
    //获得连接数据库的密码
    private static String PASS = "indbadmin";*/

  /*  static {
        try {
            //初始化JDBC驱动并让驱动加载到jvm中
            Class.forName(DRIVER);
        } catch (Exception e) {
            log.error(e.getMessage());
        }
    }*/

    /**
     * 测试连接是否成功
     *
     * @param driver
     * @param url
     * @param username
     * @param password
     * @return
     * @throws Exception
     */
    public static Boolean testConnection(String driver, String url, String username, String password) throws Exception {
        try {
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url, username, password);
            if (null == connection) {
                return false;
            }
        } catch (Exception e) {
            log.error(e.getMessage());
            return false;
        }
        return true;
    }


    public static Connection getConnection(String driver, String url, String username, String password) throws Exception {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return conn;
    }

    //关闭连接
    public static void close(Object o) throws Exception {
        if (o == null) {
            return;
        }
        if (o instanceof ResultSet) {
            try {
                ((ResultSet) o).close();
            } catch (Exception e) {
                throw new Exception(e.getMessage());
            }
        } else if (o instanceof Statement) {
            try {
                ((Statement) o).close();
            } catch (Exception e) {
                throw new Exception(e.getMessage());
            }
        } else if (o instanceof Connection) {
            Connection c = (Connection) o;
            try {
                if (!c.isClosed()) {
                    c.close();
                }
            } catch (Exception e) {
                throw new Exception(e.getMessage());
            }
        }
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) throws Exception {
        close(rs);
        close(stmt);
        close(conn);
    }

    public static void close(ResultSet rs, Connection conn) throws Exception {
        close(rs);
        close(conn);
    }

    /**
     * @throws
     * @Description: 获取数据库相关信息
     */
    public static void getDataBaseInfo(Connection conn) throws Exception {
        // Connection conn = getConnection();
        ResultSet rs = null;
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            System.out.println("数据库已知的用户: " + dbmd.getUserName());
            System.out.println("数据库的系统函数的逗号分隔列表: " + dbmd.getSystemFunctions());
            System.out.println("数据库的时间和日期函数的逗号分隔列表: " + dbmd.getTimeDateFunctions());
            System.out.println("数据库的字符串函数的逗号分隔列表: " + dbmd.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: " + dbmd.getSchemaTerm());
            System.out.println("数据库URL: " + dbmd.getURL());
            System.out.println("是否允许只读:" + dbmd.isReadOnly());
            System.out.println("数据库的产品名称:" + dbmd.getDatabaseProductName());
            System.out.println("数据库的版本:" + dbmd.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + dbmd.getDriverName());
            System.out.println("驱动程序的版本:" + dbmd.getDriverVersion());

            System.out.println("数据库中使用的表类型");
            rs = dbmd.getTableTypes();
            while (rs.next()) {
                System.out.println(rs.getString("TABLE_TYPE"));
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            JdbcUtil.close(rs);
        }
    }

    /**
     * @throws
     * @Description:获得数据库中所有Schemas(对应于oracle中的Tablespace)
     */
    public static String getSchemasInfo(Connection conn) throws Exception {
        //Connection conn = getConnection();
        ResultSet rs = null;
        String tableSchem = null;
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            rs = dbmd.getSchemas();
            while (rs.next()) {
                tableSchem = rs.getString("TABLE_SCHEM");
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            JdbcUtil.close(rs);
        }
        return tableSchem;
    }


    /**
     * @throws
     * @Description: 获取数据库中所有的表信息
     */
    public static List<TableInfo> getTablesList(Connection conn, String name) throws Exception {
        //Connection conn = getConnection();
        ResultSet rs = null;
        List<TableInfo> list = new ArrayList<>();
        try {
            /**
             * 设置连接属性,使得可获取到表的REMARK(备注)
             */
            ((OracleConnection) conn).setRemarksReporting(true);
            DatabaseMetaData dbmd = conn.getMetaData();
            String[] types = {"TABLE"};
            rs = dbmd.getTables(null, conn.getMetaData().getUserName(), name + "%", types);
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");  //表名
                String tableType = rs.getString("TABLE_TYPE");  //表类型
                String remarks = rs.getString("REMARKS");       //表备注
                if (StringUtils.isBlank(tableName)) {
                    continue;
                }
                // System.out.println(tableName + " - " + tableType + " - " + remarks);
                TableInfo tableInfo = new TableInfo();
                tableInfo.setName(tableName);
                tableInfo.setRemarks(remarks);
                list.add(tableInfo);
            }
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            JdbcUtil.close(rs);
        }
        return list;
    }

    /**
     * @throws
     * @Description: 获取某表信息
     */
    public static TableInfo getTablesInfo(String tableName, Connection conn) throws Exception {
        //Connection conn = getConnection();
        ResultSet rs = null;
        TableInfo tableInfo = new TableInfo();
        try {
            /**
             * 设置连接属性,使得可获取到表的REMARK(备注)
             */
            ((OracleConnection) conn).setRemarksReporting(true);
            DatabaseMetaData dbmd = conn.getMetaData();
            /**
             * 获取给定类别中使用的表的描述。
             * 方法原型:ResultSet getTables(String catalog,String schemaPattern,String tableNamePattern,String[] types);
             * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
             * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
             * tableNamePattern - 表名称;可包含单字符通配符("_"),或多字符通配符("%");
             * types - 表类型数组; "TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM";null表示包含所有的表类型;可包含单字符通配符("_"),或多字符通配符("%");
             */
            rs = dbmd.getTables(null, conn.getMetaData().getUserName(), tableName, new String[]{"TABLE", "VIEW"});
            if (rs.next()) {
                //表类别(可为null)
                tableInfo.setTableCat(rs.getString("TABLE_CAT"));
                //表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
                tableInfo.setTableSchemaName(rs.getString("TABLE_SCHEM"));
                //表名
                tableInfo.setName(rs.getString("TABLE_NAME"));
                //表类型,典型的类型是 "TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"。
                tableInfo.setTableType(rs.getString("TABLE_TYPE"));
                //表备注
                tableInfo.setRemarks(rs.getString("REMARKS"));
            }
            List<IndexInfo> list = JdbcUtil.getIndexInfo(tableName, conn);
            tableInfo.setListIndexInfo(list);
        } catch (Exception ex) {
            log.error("获取表" + tableName + "信息失败" + ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            JdbcUtil.close(rs);
        }
        return tableInfo;
    }

    /**
     * @throws
     * @Description: 获取表主键信息
     */
    public static Map<String, String> getPrimaryKeysInfo(String tableName, Connection conn) throws Exception {
        // Connection conn = getConnection();
        ResultSet rs = null;
        Map<String, String> map = new HashMap<>();
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            /**
             * 获取对给定表的主键列的描述
             * 方法原型:ResultSet getPrimaryKeys(String catalog,String schema,String table);
             * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
             * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
             * table - 表名称;可包含单字符通配符("_"),或多字符通配符("%");
             */
            rs = dbmd.getPrimaryKeys(null, conn.getMetaData().getUserName(), tableName);

            while (rs.next()) {
                // String tableCat = rs.getString("TABLE_CAT");  //表类别(可为null)
                //String tableSchemaName = rs.getString("TABLE_SCHEM");//表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
                // String tableName = rs.getString("TABLE_NAME");  //表名
                String columnName = rs.getString("COLUMN_NAME");//列名
                short keySeq = rs.getShort("KEY_SEQ");//序列号(主键内值1表示第一列的主键，值2代表主键内的第二列)
                String pkName = rs.getString("PK_NAME"); //主键名称
                if (StringUtils.isBlank(columnName)) {
                    continue;
                }
                map.put(columnName, pkName);
            }
        } catch (Exception ex) {
            log.error("获取表" + tableName + "主键信息失败！" + ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            JdbcUtil.close(rs);
        }
        return map;
    }

    /**
     * @throws
     * @Description: 获取表索引信息
     */
    public static List<IndexInfo> getIndexInfo(String tableName, Connection conn) throws Exception {
        //Connection conn = getConnection();
        ResultSet rs = null;
        List<IndexInfo> list = new ArrayList<>();
        try {
            DatabaseMetaData dbmd = conn.getMetaData();
            /**
             * 获取给定表的索引和统计信息的描述
             * 方法原型:ResultSet getIndexInfo(String catalog,String schema,String table,boolean unique,boolean approximate)
             * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
             * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
             * table - 表名称;可包含单字符通配符("_"),或多字符通配符("%");
             * unique - 该参数为 true时,仅返回唯一值的索引; 该参数为 false时,返回所有索引;
             * approximate - 该参数为true时,允许结果是接近的数据值或这些数据值以外的值;该参数为 false时,要求结果是精确结果;
             */
            rs = dbmd.getIndexInfo(null, conn.getMetaData().getUserName(), tableName, false, true);
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");//列名;TYPE为 tableIndexStatistic时列名称为 null;
                if (StringUtils.isBlank(columnName)) {
                    continue;
                }
                String tableCat = rs.getString("TABLE_CAT");  //表类别(可为null)
                String tableSchemaName = rs.getString("TABLE_SCHEM");//表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
                // String tableName = rs.getString("TABLE_NAME");  //表名
                boolean nonUnique = rs.getBoolean("NON_UNIQUE");// 索引值是否可以不唯一,TYPE为 tableIndexStatistic时索引值为 false;
                if (StringUtil.oneCase(columnName, "ID,FID")) { // 主键索引不保存
                    continue;
                }
                String indexQualifier = rs.getString("INDEX_QUALIFIER");//索引类别（可能为空）,TYPE为 tableIndexStatistic 时索引类别为 null;
                String indexName = rs.getString("INDEX_NAME");//索引的名称 ;TYPE为 tableIndexStatistic 时索引名称为 null;
                /**
                 * 索引类型：
                 *  tableIndexStatistic - 此标识与表的索引描述一起返回的表统计信息
                 *  tableIndexClustered - 此为集群索引
                 *  tableIndexHashed - 此为散列索引
                 *  tableIndexOther - 此为某种其他样式的索引
                 */
                short type = rs.getShort("TYPE");//索引类型;
                short ordinalPosition = rs.getShort("ORDINAL_POSITION");//在索引列顺序号;TYPE为 tableIndexStatistic 时该序列号为零;
                String ascOrDesc = rs.getString("ASC_OR_DESC");//列排序顺序:升序还是降序[A:升序; B:降序];如果排序序列不受支持,可能为 null;TYPE为 tableIndexStatistic时排序序列为 null;
                int cardinality = rs.getInt("CARDINALITY");   //基数;TYPE为 tableIndexStatistic 时,它是表中的行数;否则,它是索引中唯一值的数量。
                int pages = rs.getInt("PAGES"); //TYPE为 tableIndexStatisic时,它是用于表的页数,否则它是用于当前索引的页数。
                String filterCondition = rs.getString("FILTER_CONDITION"); //过滤器条件,如果有的话(可能为 null)。
                IndexInfo indexInfo = new IndexInfo();
                indexInfo.setColumnName(columnName);
                indexInfo.setIndexName(indexName);
                indexInfo.setIndexQualifier(indexQualifier);
                indexInfo.setIndexType(String.valueOf(type));
                indexInfo.setOrdinalPosition(String.valueOf(ordinalPosition));
                indexInfo.setAscOrDesc(ascOrDesc);
                list.add(indexInfo);
            }
        } catch (Exception ex) {
            log.error("获取表" + tableName + "索引信息失败！" + ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            JdbcUtil.close(rs);
        }
        return list;
    }

    /**
     * @throws
     * @Description: 获取表中列值信息
     */
    public static List<ColumnInfo> getColumnsInfo(String tableName, Connection conn) throws Exception {
        //Connection conn = getConnection();
        ResultSet rs = null;
        List<ColumnInfo> list = new ArrayList<>();
        try {
            /**
             * 设置连接属性,使得可获取到列的REMARK(备注)
             */
            ((OracleConnection) conn).setRemarksReporting(true);
            DatabaseMetaData dbmd = conn.getMetaData();
            /**
             * 获取可在指定类别中使用的表列的描述。
             * 方法原型:ResultSet getColumns(String catalog,String schemaPattern,String tableNamePattern,String columnNamePattern)
             * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
             * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
             * tableNamePattern - 表名称;可包含单字符通配符("_"),或多字符通配符("%");
             * columnNamePattern - 列名称; ""表示获取列名为""的列(当然获取不到);null表示获取所有的列;可包含单字符通配符("_"),或多字符通配符("%");
             */
            rs = dbmd.getColumns(null, conn.getMetaData().getUserName(), tableName, null);

            while (rs.next()) {
                String tableCat = rs.getString("TABLE_CAT");  //表类别（可能为空）
                String tableSchemaName = rs.getString("TABLE_SCHEM");  //表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知
                String tableName_ = rs.getString("TABLE_NAME");  //表名
                String columnName = rs.getString("COLUMN_NAME");  //列名
                int dataType = rs.getInt("DATA_TYPE");     //对应的java.sql.Types的SQL类型(列类型ID)
                String dataTypeName = rs.getString("TYPE_NAME");  //java.sql.Types类型名称(列类型名称)
                int columnSize = rs.getInt("COLUMN_SIZE");  //列大小
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");  //小数位数
                int numPrecRadix = rs.getInt("NUM_PREC_RADIX");  //基数（通常是10或2） --未知
                /**
                 *  0 (columnNoNulls) - 该列不允许为空
                 *  1 (columnNullable) - 该列允许为空
                 *  2 (columnNullableUnknown) - 不确定该列是否为空
                 */
                int nullAble = rs.getInt("NULLABLE");  //是否允许为null
                String remarks = rs.getString("REMARKS");  //列描述
                String columnDef = rs.getString("COLUMN_DEF");  //默认值
                int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");    // 对于 char 类型，该长度是列中的最大字节数
                int ordinalPosition = rs.getInt("ORDINAL_POSITION");   //表中列的索引（从1开始）
                /**
                 * ISO规则用来确定某一列的是否可为空(等同于NULLABLE的值:[ 0:'YES'; 1:'NO'; 2:''; ])
                 * YES -- 该列可以有空值;
                 * NO -- 该列不能为空;
                 * 空字符串--- 不知道该列是否可为空
                 */
                String isNullAble = rs.getString("IS_NULLABLE");

                /**
                 * 指示此列是否是自动递增
                 * YES -- 该列是自动递增的
                 * NO -- 该列不是自动递增
                 * 空字串--- 不能确定该列是否自动递增
                 */
                //String isAutoincrement = rs.getString("IS_AUTOINCREMENT");   //该参数测试报错
                ColumnInfo col = new ColumnInfo();
                col.setPrimaryKey(2);
                col.setDefaultValue(columnDef);
                col.setRemarks(remarks);
                col.setName(columnName);
                col.setDataType(dataType);
                col.setDataTypeName(dataTypeName);
                col.setPrecision(columnSize);
                col.setScale(decimalDigits);
                col.setIsNull(0 == nullAble ? "2" : "1");
                list.add(col);
            }
            //主键信息
            Map<String, String> mapPrimaryKeys = JdbcUtil.getPrimaryKeysInfo(tableName, conn);
            for (ColumnInfo columnInfo : list) {
                if (null == columnInfo || StringUtils.isBlank(columnInfo.getName())) {
                    continue;
                }
                if (mapPrimaryKeys.containsKey(columnInfo.getName())) {
                    columnInfo.setPrimaryKey(1);
                }
            }
        } catch (Exception ex) {
            log.error("获取表" + tableName + "列值信息失败！" + ex.getMessage());
            throw new Exception(ex.getMessage());
        } finally {
            JdbcUtil.close(rs);
        }
        return list;
    }

    /**
     * 解析sql 语句 里用到的表
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public static Map<TableStat.Name, TableStat> getSqlTableInfoMap(String sql, String dbType) throws Exception {
        Map<TableStat.Name, TableStat> map = null;
        try {
            if (StringUtils.isBlank(sql)) {
                return null;
            }
            try {
                if (StringUtils.isBlank(dbType)) {
                    dbType = JdbcConstants.ORACLE;
                }
                //格式化输出
                String result = SQLUtils.format(sql, dbType);
                log.info("待解析的sql语句===========>" + result);
                List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
                //解析出的独立语句的个数
                log.info("解析出的独立语句的个数===========>" + stmtList.size());
                for (int i = 0; i < stmtList.size(); i++) {
                    SQLStatement stmt = stmtList.get(i);
                    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
                    stmt.accept(visitor);
                    //获取表名称
                    log.info("解析出的表名===========>" + visitor.getTables());
                    map = visitor.getTables();
                }
            } catch (Exception ex) {
                log.error("解析语句出错！" + ex.getMessage());
            }
            if (CollectionUtils.isEmpty(map)) {
                log.error("该语句没有解析出来表信息");
            }
        } catch (Exception ex) {
            log.error("获取表信息出错" + ex.getMessage());
        }
        return map;
    }

    /**
     * 执行sql语句
     *
     * @param sqls
     */

    public static String executeSql(String sqls, Connection conn) throws Exception {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sqls, JdbcConstants.ORACLE);
            for (SQLStatement sqlStatement : sqlStatements) {
                sqlStatement.setAfterSemi(false);
                String sql = sqlStatement.toString();
                if (StringUtils.isBlank(sql)) {
                    continue;
                }
                sql = sql.replaceAll("`", "");
                statement.addBatch(sql);
            }
            statement.executeBatch();
            return null;
        } catch (Exception ex) {
            log.error("执行sql语句失败！" + sqls + ex.getMessage());
            return "执行sql语句失败！[" + sqls + "]\r\n失败原因：" + ex.getMessage() + "\r\n";
        } finally {
            JdbcUtil.close(statement);
        }
    }

    public static Boolean executeSqlBoolean(String sqls, Connection conn) throws Exception {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sqls, JdbcConstants.ORACLE);
            for (SQLStatement sqlStatement : sqlStatements) {
                sqlStatement.setAfterSemi(false);
                String sql = sqlStatement.toString();
                if (StringUtils.isBlank(sql)) {
                    continue;
                }
                sql = sql.replaceAll("`", "");
                statement.addBatch(sql);
            }
            statement.executeBatch();
            return true;
        } catch (Exception ex) {
            log.error("执行sql语句失败！" + sqls + ex.getMessage());
            return false;
        } finally {
            JdbcUtil.close(statement);
        }
    }

    /**
     * 通过表名解析表信息和字段信息
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public static Map<TableInfo, List<ColumnInfo>> getTableInfoMap(String tableName, Connection conn) throws Exception {
        Map<TableInfo, List<ColumnInfo>> stringMap = null;
        try {
            TableInfo info = JdbcUtil.getTablesInfo(tableName, conn);
            List<ColumnInfo> listColumn = JdbcUtil.getColumnsInfo(tableName, conn);
            stringMap = new HashMap<>();
            stringMap.put(info, listColumn);
        } catch (Exception ex) {
            log.error(ex.getMessage());
        }
        return stringMap;
    }

    /**
     * 通过sql语句解析表信息
     * BETWEEN '${FBEGINDATE}' AND '${FENDDATE}'
     *
     * @param list
     * @return
     * @throws Exception
     */
    public static Map<TableInfo, List<ColumnInfo>> getTableInfoMap(List<TableInfo> list, Connection conn) throws Exception {
        Map<TableInfo, List<ColumnInfo>> stringMap = null;
        try {
            if (CollectionUtils.isEmpty(list)) {
                return null;
            }
            stringMap = new LinkedHashMap<>();
            String dbType = conn.getMetaData().getDatabaseProductName().toLowerCase();
            for (TableInfo tableInfo : list) {
                if (null == tableInfo) {
                    continue;
                }
                String sql = tableInfo.getSql();
                if (StringUtils.isBlank(sql)) {
                    continue;
                }
                Map<TableStat.Name, TableStat> map = JdbcUtil.getSqlTableInfoMap(sql, dbType);
                if (CollectionUtils.isEmpty(map)) {
                    log.error("该语句没有解析出来表信息");
                    continue;
                }
                //获取报表落地表信息
                if (StringUtils.isNotBlank(tableInfo.getReportTablename())) {
                    TableInfo info = JdbcUtil.getTablesInfo(tableInfo.getReportTablename(), conn);
                    info.setReportTablename("6");
                    List<ColumnInfo> listColumn = null;
                    try {
                        listColumn = JdbcUtil.getColumnsInfo(tableInfo.getReportTablename(), conn);
                        stringMap.put(info, listColumn);
                    } catch (Exception ex) {
                        log.error("获取表字段信息失败！" + tableInfo.getReportTablename());
                        stringMap.put(info, null);
                    }
                }
                for (Map.Entry<TableStat.Name, TableStat> entry : map.entrySet()) {
                    String tableName = entry.getKey().getName();
                    if (StringUtils.isBlank(tableName)) {
                        continue;
                    }
                    TableInfo info = JdbcUtil.getTablesInfo(tableName, conn);
                    info.setKeyName(tableInfo.getKeyName());
                    info.setReportName(tableInfo.getReportName());
                    info.setSql(tableInfo.getSql());
                    List<ColumnInfo> listColumn = null;
                    try {
                        listColumn = JdbcUtil.getColumnsInfo(tableName, conn);
                        stringMap.put(info, listColumn);
                    } catch (Exception ex) {
                        log.error("获取表字段信息失败！" + tableName);
                        stringMap.put(info, null);
                    }

                }
            }
        } catch (Exception ex) {
            log.error("获取表信息出错" + ex.getMessage());
        }
        return stringMap;
    }

    /**
     * 获取数据库表的 字段和对应的类型
     * 查询的时候 增加 LONGTEXT 在页面显示文本域
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public static Map<String, String> getTableColumnNames(String sql, Connection connection) throws Exception {
        PreparedStatement pStemt = null;
        Map<String, String> columnNames = new LinkedHashMap<>();
        Map<String, Integer> columnCount = new HashMap<>();
        try {
            if (StringUtils.isBlank(sql)) {
                throw new BusinessException("没有获取到SQL语句！");
            }
            pStemt = connection.prepareStatement(sql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                if (columnNames.containsKey(rsmd.getColumnName(i + 1))) {
                    int count = columnCount.get(rsmd.getColumnName(i + 1)) + 1;
                    columnNames.put(rsmd.getColumnName(i + 1) + count, rsmd.getColumnTypeName(i + 1));
                    columnCount.put(rsmd.getColumnName(i + 1), count);
                } else {
                    columnNames.put(rsmd.getColumnName(i + 1), rsmd.getColumnTypeName(i + 1));
                    columnCount.put(rsmd.getColumnName(i + 1), 0);
                }
            }
            if (null == columnNames || columnNames.size() == 0) {
                throw new BusinessException("没有获取到表" + sql + "字段和字段类型！");
            }
        } catch (Exception ex) {
            log.error("获取表" + sql + "字段和字段类型出错出错： " + ex);
            throw new BusinessException("获取表" + sql + "字段和字段类型出错出错： " + ex);
        } finally {
            if (pStemt != null) {   // 关闭声明
                pStemt.close();
            }
        }
        return columnNames;
    }

    /**
     * SQL语句拼接属性值
     *
     * @param number
     * @param dataNumber
     * @param dataTypeName
     * @param list
     * @param pStemt
     * @throws Exception
     */
    public static void addPreparedStatement(int number, int dataNumber, String dataTypeName, List<Object> list, PreparedStatement pStemt) throws Exception {
        addPreparedStatement(number, dataNumber, dataTypeName, list, pStemt, false);
    }

    /**
     * SQL语句拼接属性值
     *
     * @param number        赋值？的顺序 开始
     * @param dataNumber    取值的顺序 开始
     * @param dataTypeName  数据库类型
     * @param list          数据
     * @param pStemt
     * @param queryOrUpdate 查询和修改的条件 设置为空
     * @throws Exception
     */
    public static void addPreparedStatement(int number, int dataNumber, String dataTypeName, List<Object> list, PreparedStatement pStemt, boolean queryOrUpdate) throws Exception {
        switch (dataTypeName) {
            case "VARCHAR":
                if (null != list.get(dataNumber) && StringUtils.isNotBlank(list.get(dataNumber).toString())) {
                    pStemt.setString(number + 1, list.get(dataNumber).toString());
                } else if (queryOrUpdate) {
                    pStemt.setString(number + 1, "");
                } else {
                    pStemt.setString(number + 1, null);
                }
                break;
            case "DECIMAL":
                if (null != list.get(dataNumber) && StringUtils.isNotBlank(list.get(dataNumber).toString())) {
                    pStemt.setBigDecimal(number + 1, new BigDecimal(list.get(dataNumber).toString()));
                } else if (queryOrUpdate) {
                    pStemt.setString(number + 1, "");
                } else {
                    pStemt.setString(number + 1, null);
                }
                break;
            case "DATETIME":
                if (null != list.get(dataNumber) && StringUtils.isNotBlank(list.get(dataNumber).toString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat(YssEnum.YSS_DEFAULT_DATE_TIME_FORMAT.getCode());
                    Date startDate = sdf.parse(list.get(dataNumber).toString());
                    long time = startDate.getTime();//返回当前日期对应的long类型的毫秒数
                    java.sql.Timestamp date = new java.sql.Timestamp(time);
                    pStemt.setTimestamp(number + 1, date);
                } else if (queryOrUpdate) {
                    pStemt.setString(number + 1, "");
                } else {
                    pStemt.setTimestamp(number + 1, null);
                }
                break;
            case "DATE":
                if (null != list.get(dataNumber) && StringUtils.isNotBlank(list.get(dataNumber).toString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat(YssEnum.YSS_DEFAULT_DATE_TIME.getCode());
                    Date startDate = sdf.parse(list.get(dataNumber).toString());
                    long time = startDate.getTime();//返回当前日期对应的long类型的毫秒数
                    java.sql.Date date = new java.sql.Date(time);
                    pStemt.setDate(number + 1, date);
                } else if (queryOrUpdate) {
                    pStemt.setString(number + 1, "");
                } else {
                    pStemt.setDate(number + 1, null);
                }
                break;
            case "TIMESTAMP":
                if (null != list.get(dataNumber) && StringUtils.isNotBlank(list.get(dataNumber).toString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat(YssEnum.YSS_DEFAULT_DATE_TIME_FORMAT.getCode());
                    Date startDate = sdf.parse(list.get(dataNumber).toString());
                    long time = startDate.getTime();//返回当前日期对应的long类型的毫秒数
                    java.sql.Timestamp date = new java.sql.Timestamp(time);
                    pStemt.setTimestamp(number + 1, date);
                } else if (queryOrUpdate) {
                    pStemt.setString(number + 1, "");
                } else {
                    pStemt.setTimestamp(number + 1, null);
                }
                break;
            default:
                throw new BusinessException("没有获取到数据库类型，请检查后重试");
        }
    }
}
